﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
using System.Reflection;
using System.Threading;

namespace StudentManage.Common
{
    public class DownToExcel
    {
        //传入DataSet和EXCEl名
        public string ToExl(DataSet ds, string excelName)
        {
            
            int tablecount = ds.Tables.Count;
            if (tablecount <= 0) return "";
         
            int index = 0;
            Microsoft.Office.Interop.Excel.Application xlApp = null;
            Microsoft.Office.Interop.Excel.Workbook xlBook = null;
            Microsoft.Office.Interop.Excel.Worksheet xlSheet = null;
            int rowIndex = 1;
            int colIndex = 0;
            object missing = Missing.Value;

            try
            {
                    xlApp = new Microsoft.Office.Interop.Excel.Application();
                    xlBook = xlApp.Workbooks.Add(true);
                    
                    foreach (System.Data.DataTable dt in ds.Tables)
                    {
                        
                            
                            index++;
                            rowIndex = 1;
                            colIndex = 0;

                            xlSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlApp.Worksheets[1];

                            xlSheet.Name = dt.TableName;

                            foreach (DataColumn Col in dt.Columns)
                            {
                                colIndex = colIndex + 1;
                                xlApp.Cells[1, colIndex] = "   " + Col.ColumnName;
                            }


                            int rowNum = dt.Rows.Count;
                            int colNum = dt.Columns.Count;
                            string[,] finalData = new string[rowNum, colNum];
                            for (int i = 0; i < rowNum; i++)
                            {
                                for (int j = 0; j < colNum; j++)
                                {
                                    finalData[i, j] = dt.Rows[i][j].ToString();
                                }
                            }


                            xlSheet.Range[xlSheet.Cells[1, 1], xlSheet.Cells[1, colIndex]].Font.Bold = true;
                            xlSheet.Range[xlSheet.Cells[1, 1], xlSheet.Cells[rowIndex, colIndex]].Borders.LineStyle = 1;
                            xlSheet.Range[xlSheet.Cells[2, 1], xlSheet.Cells[rowNum + 1, colNum]].Value2 = finalData;
                            xlSheet.Range[xlSheet.Cells[2, 1], xlSheet.Cells[rowNum + 1, colNum]].NumberFormatLocal = "@ ";

                            if (index < tablecount) xlSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlApp.Worksheets.Add(missing, missing, missing, missing);


                    }
                //保存的路径，可以根据要求更改
                if (System.IO.File.Exists(Environment.CurrentDirectory + "\\" + excelName + ".xls")) System.IO.File.Delete(Environment.CurrentDirectory + "\\" + excelName + ".xls");
                xlApp.Visible = false;
                xlSheet.SaveAs(Environment.CurrentDirectory + "\\" + excelName + ".xls", missing, missing, missing, missing, missing, missing, missing, missing, missing);
                return Environment.CurrentDirectory + "\\" + excelName + ".xls";
            }
            finally
            {
                object saveChange = true;
                xlBook.Close(saveChange, Environment.CurrentDirectory + "\\" + excelName + ".xls", missing);
                xlApp.Quit();
                if (xlSheet != null) Marshal.ReleaseComObject(xlSheet);
                if (xlBook != null) Marshal.ReleaseComObject(xlBook);
                if (xlApp != null) Marshal.ReleaseComObject(xlApp);
                xlApp = null;
                xlBook = null;
                xlSheet = null;
                GC.Collect();
            }
        }
    }
}
